#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def Month_End_Risk_Report(ref_date, portf_list):

	tplt_file = TPLT_PATH + 'Month End Risk Report.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	
	for portf_id in portf_list:
	
		xBook = xApp.Workbooks.Open(tplt_file)
		xSht = xBook.Worksheets('Report')
		
		hld = query_hld(ref_date, [portf_id])
		pnl = query_pnl(date(ref_date.year, ref_date.month, 1), ref_date, [portf_id])
		pref_av = query_pref_AV(ref_date, [portf_id])[0][1]
		
		xSht.Cells(5, 4).Value = portf_id
		xSht.Cells(6, 4).Value = ref_date
		xSht.Cells(5, 7).Value = pref_av/1000000
		
		expo = {} # expo[(ticker, l/s)] = mkt_exposure
		for elem in hld:
			expo[(elem[0], elem[3])] = elem[12]
		
		blp_tick = []
		blp_eq_tick = []
		rcd = {}

		for elem in pnl:
			ticker = elem[0]
			sec_type = elem[2]
			long_short = elem[3]
			ret = elem[6]
			trd_curncy = elem[7]
			base_curncy = elem[8]
			exp = expo[(ticker, long_short)] if expo.has_key((ticker, long_short)) else 0
			last_price = query_latest_price(ref_date, ticker)
			
			#blp ticker
			blp_ticker = ticker
			if (sec_type == 'PNOTE') or (sec_type == 'STOCK' and trd_curncy == 'CNY'):
				blp_ticker = ch_ticker_to_blp_ticker(ticker)
			if (sec_type == 'STOCK' and trd_curncy == 'HKD'):
				blp_ticker = hk_ticker_to_blp_ticker(ticker)
			
			region = sec_region(blp_ticker)
			
			blp_tick.append(blp_ticker)
			if is_equity(blp_ticker):
				blp_eq_tick.append(blp_ticker)
				
			rcd[blp_ticker] = [long_short, exp, ret, last_price, base_curncy, region]
			
		sec_name_dict = query_blp_static(blp_tick, ['NAME'])
		sec_curncy_dict = query_blp_static(blp_tick, ['CRNCY'])		
		sec_sector_dict = query_blp_static(blp_eq_tick, ['ICB_SUPERSECTOR_NUM', 'GICS_SECTOR_NAME', 'GICS_SUB_INDUSTRY_NAME'])
		mkt_cap_dict = query_blp_latest(blp_eq_tick, ['CUR_MKT_CAP'], ref_date)
		volume_avg_3m_dict = query_blp_average(blp_eq_tick, ['PX_VOLUME'], ref_date - timedelta(days=90), ref_date)
		
		row = 5
		for tick in rcd.keys():
			long_short = rcd[tick][0]
			exp = rcd[tick][1] 
			ret = rcd[tick][2]
			last_price = rcd[tick][3] 
			base_curncy = rcd[tick][4] 
			region = rcd[tick][5]
			sec_name = sec_name_dict[tick]['NAME']
			local_curncy = sec_curncy_dict[tick]['CRNCY']
			fx = query_latest_fx(ref_date, base_curncy, local_curncy)
			icb_num = sec_sector_dict[tick]['ICB_SUPERSECTOR_NUM'] if sec_sector_dict.has_key(tick) else 'Others'
			gics_name = sec_sector_dict[tick]['GICS_SECTOR_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
			gics_sub = sec_sector_dict[tick]['GICS_SUB_INDUSTRY_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
					
			if mkt_cap_dict.has_key(tick):
				mktcap_local = mkt_cap_dict[tick]['CUR_MKT_CAP']
				mktcap_base = mktcap_local / fx
			else:
				mktcap_local = 'Others'
				mktcap_base = 'Others'
				
			if volume_avg_3m_dict.has_key(tick):
				volume_avg_3m = volume_avg_3m_dict[tick]['PX_VOLUME']
				xSht.Cells(row, 26).Formula = '=Y' + str(row) + '*V' + str(row) + '/X' + str(row)
				xSht.Cells(row, 27).Formula = '=' + str(abs(pref_av*exp)) + '/(Z' + str(row) + '*0.25)'
			else:
				volume_avg_3m = 'Others'
				xSht.Cells(row, 26).Value = 'Others'
				xSht.Cells(row, 27).Value = 'Others'
	
			xSht.Range(xSht.Cells(row, 12), xSht.Cells(row, 25)).Value = (tick, region, exp, exp, ret, long_short,
			sec_name, icb_num, mktcap_local, mktcap_base, last_price, local_curncy, fx, volume_avg_3m)
			
			xSht.Cells(row, 28).Value = gics_name
			xSht.Cells(row, 29).Value = gics_sub
			
			row += 1
		
		rept_file = REPT_PATH + 'Month End Risk Report_' + portf_id + '_' + ref_date.isoformat() + '.xls'
		xBook.SaveAs(rept_file)
		xBook.Close()

		print 'info, month end risk report completed for ', portf_id, ' at ', ref_date.isoformat()	
	del xApp